package com.github.data_manage.dao

import com.github.data_manage.domain.po.PersonWorkExperience
import org.springframework.data.jpa.repository.Modifying
import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.query.Param
import java.time.LocalDate

/**
 *@author CHN
 *@since 2024-08-07 22:07
 */
interface PersonWorkExperienceDao : BaseDao<PersonWorkExperience> {

    fun removeAllByPersonIdEquals(personId: Int)

    @Query("select p from PersonWorkExperience p where p.personId = :personId order by p.startDate desc nulls last ")
    fun findAllByPersonIdEquals(personId: Int): List<PersonWorkExperience>

    fun countByPersonIdAndCompanyId(personId: Int, companyId: Int): Int

    fun findAllByCompanyIdEqualsAndEndDateBetween(
        companyId: Int,
        startDate: LocalDate,
        endDate: LocalDate
    ): List<PersonWorkExperience>

//        and ((p.endDate between :startDate and :endDate or p.startDate >= :startDate) or (p.endDate is null or p.endDate >= :startDate))

    @Query(
        """
        select p from PersonWorkExperience p where p.companyId = :companyId and p.personId != :personId
    """
    )
    fun findColleagues(
        @Param("companyId") companyId: Int,
        @Param("personId") personId: Int,
//                       @Param("startDate") startDate: LocalDate?,
//                       @Param("endDate") endDate: LocalDate?
    ): List<PersonWorkExperience>

    @Modifying
    @Query("update PersonWorkExperience set companyName = :companyName where companyId = :companyId")
    fun updateCompanyNameByCompanyId(companyId: Int, companyName: String)

    @Modifying
    @Query("update PersonWorkExperience set companyId = :mainCompanyId,companyName = :companyName where companyId = :minorCompanyId")
    fun mergeCompany(
        @Param("mainCompanyId") mainCompanyId: Int,
        @Param("companyName") companyName: String,
        @Param("minorCompanyId") minorCompanyId: Int
    )
}